﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using QAAdmin.Util;
using System.IO;
using System.Data;
using System.Linq;

namespace QAAdmin.BusinessObject
{
    class DAO
    {
        private static readonly string URI_PREFIX = "file:///";
        private static readonly string URI_PATH_SEPARATOR = "/";
        private static readonly string ABSOLUTE_PATH_SEPARATOR = "\\";
        private static string configFile = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.ToString() + ".config";

        internal static IList<QAJob> GetJobByType(string tipe)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT job.JOBID, job.DOCID, job.NAMAPTKP, job.NPWP, job.MASAPAJAK, job.WAKTUPENUGASAN, job.DITUGASKANKEPADA, 
job.STATUSASAL, job.STATUSAKHIR, job.WAKTUPENGECEKAN, job.CATATAN, job.BARCODE, job.JENISSPT, sptperbaikan.JUMLAHPERBAIKAN 
FROM QA_JOB job LEFT OUTER JOIN VW_SPTPERBAIKAN sptperbaikan ON job.DOCID = sptperbaikan.DOCID AND job.BARCODE = sptperbaikan.BARCODE 
WHERE job.STATUSASAL=:STATUSASAL ORDER BY job.JOBID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":STATUSASAL", tipe);
                    //cmd.Parameters.AddWithValue(":JENISSPT", QAController.JenisSPT);
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAJob> retval = new List<QAJob>();
                    while (reader.Read())
                    {
                        QAJob job = CreateQAJobObject(reader);

                        retval.Add(job);
                    }

                    return retval;
                }
            }
        }

        private static QAJob CreateQAJobObject(OracleDataReader reader)
        {
            QAJob job = new QAJob();
            job.JobID = reader["JOBID"].ToString();
            job.DocId = reader["DOCID"].ToString();
            job.NamaPTKP = reader["NAMAPTKP"].ToString();
            job.Npwp = reader["NPWP"].ToString();
            job.MasaPajak = reader["MASAPAJAK"].ToString();
            job.WaktuPenugasan = (DateTime)reader["WAKTUPENUGASAN"];
            job.DitugaskanKepada = reader["DITUGASKANKEPADA"].ToString();
            job.StatusAsal = reader["STATUSASAL"].ToString();
            job.StatusAkhir = reader["STATUSAKHIR"] is DBNull ? "" : reader["STATUSAKHIR"].ToString();
            job.WaktuPengecekan = reader["WAKTUPENGECEKAN"] is DBNull ? DateTime.MaxValue : (DateTime)reader["WAKTUPENGECEKAN"];
            job.Catatan = reader["CATATAN"] is DBNull ? "" : reader["CATATAN"].ToString();
            job.Barcode = reader["BARCODE"] is DBNull ? "" : reader["BARCODE"].ToString();
            job.JumlahPerbaikan = reader["JUMLAHPERBAIKAN"] is DBNull ? "" : reader["JUMLAHPERBAIKAN"].ToString();
            job.JenisSPT = reader["JENISSPT"] is DBNull ? "" : reader["JENISSPT"].ToString();
            return job;
        }

        internal static IList<QAJob> GetNewUnbalancedJob(int jumlahSPT)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                string sql = "";
                string[] jenisSPT = ConfigurationManager.AppSettings["JenisSPT"].Split(',');
                string query = "";
                foreach (string s in jenisSPT)
                {
                    if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings[s]))
                    {
                        query += ConfigurationManager.AppSettings[s];
                        //query += " WHERE DITUGASKANKEPADA is null "; //modified by Vincent 20130124 untuk solve issue ganti petugas yg dioper hanya yg belum pass saja
                        query += " WHERE DITUGASKANKEPADA is null ";
                        query += " and UPPER(BALANCE)='FALSE'";
                        query += " OR (STATUSAKHIR LIKE :STATUSAKHIR) ";
                        sql = query;
                        query += " UNION ";
                    }
                }
                if (!string.IsNullOrEmpty(sql))
                    sql = "SELECT * FROM (" + sql + ") WHERE ROWNUM <=" + jumlahSPT + " ORDER BY JENISSPT ";

                /*  if (QAController.JenisSPT == "1111 DM")
                      sql = @"select d.DOCID, d.BARCODE, d.DM_NAMAPKP_11, d.DM_NPWP_11, d.DM_MASA1_11, d.DM_MASA2_11, d.DM_TAHUN_11 
                                  from QA_USER.qa_job q right join dmindex_11 d on q.DOCID=d.DOCID 
                                  where q.DITUGASKANKEPADA is null and UPPER(BALANCE)='FALSE' AND ROWNUM <= " + jumlahSPT;
                  else
                      sql += @"select d.DOCID, d.BARCODE, d.P_NAMAPKP_11, d.P_NPWP_11, d.P_MASA1_11, d.P_MASA2_11, d.P_TAHUN_11 
                                  from QA_USER.qa_job q right join ppnindex_11 d on q.DOCID=d.DOCID 
                                  where q.DITUGASKANKEPADA is null and UPPER(BALANCE)='FALSE' AND ROWNUM <= " + jumlahSPT;
  */
                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":STATUSAKHIR", TipeSPT.Unbalance.ToString().ToLower());
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAJob> retval = new List<QAJob>();
                    while (reader.Read())
                    {
                        QAJob j = new QAJob();
                        j.DocId = reader["DOCID"] is DBNull ? "" : reader["DOCID"].ToString();
                        j.Barcode = reader["BARCODE"] is DBNull ? "" : reader["BARCODE"].ToString();
                        j.NamaPTKP = reader["NAMAPKP"] is DBNull ? "" : reader["NAMAPKP"].ToString();
                        j.Npwp = reader["NPWP"] is DBNull ? "" : reader["NPWP"].ToString();
                        j.MasaPajak = reader["MASA1"] + " s/d " + reader["MASA2"] + " " + reader["TAHUN"];
                        j.DitugaskanKepada = reader["DITUGASKANKEPADA"] is DBNull ? "" : reader["DITUGASKANKEPADA"].ToString();
                        j.JenisSPT = reader["JENISSPT"] is DBNull ? "" : reader["JENISSPT"].ToString();

                        retval.Add(j);
                    }

                    return retval;
                }
            }
        }

        internal static IList<QAJob> GetNewBalancedJob(double faktorPersentase, int jumlahSPT)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();

                IList<string> lstDocId = GetTotalNewBalancedJob();
                double jumlahPopulasi = lstDocId.Count;
                double dbJumlahSampel = jumlahPopulasi / (1 + jumlahPopulasi * Math.Pow(faktorPersentase, 2d));
                int jumlahSampel = (int)Math.Truncate(dbJumlahSampel);

                Dictionary<int, string> lstSelectedDocId = new Dictionary<int, string>();
                Random rnd = new Random();
                for (int i = 0; i < jumlahSampel; i++)
                {
                    int n = rnd.Next(0, lstDocId.Count);
                    while (lstSelectedDocId.ContainsKey(n))
                        n = rnd.Next(0, lstDocId.Count);

                    lstSelectedDocId.Add(n, lstDocId[n]);
                }

                string tmpSql = "";
                foreach (int key in lstSelectedDocId.Keys)
                    tmpSql += "'" + lstSelectedDocId[key] + "', ";
                if (tmpSql.Length > 0)
                    tmpSql = " and d.DOCID in (" + tmpSql.Substring(0, tmpSql.Length - 2) + ")";

                string sql = "";
                string[] jenisSPT = ConfigurationManager.AppSettings["JenisSPT"].Split(',');
                StringBuilder query = new StringBuilder("SELECT * FROM (");
                for (int i = 0; i < jenisSPT.Length; i++)
                {
                    string s = jenisSPT[i];
                    if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings[s]))
                    {
                        if (i != 0)
                        {
                            query.Append(" UNION ");
                        }
                        query.Append(ConfigurationManager.AppSettings[s]);
                    }
                }
                query.Append(")");
                //query.Append(" WHERE DITUGASKANKEPADA is null "); //modified by Vincent 20130124 untuk solve issue ganti petugas yg dioper hanya yg belum pass saja
                query.Append(" WHERE DITUGASKANKEPADA is null ");
                query.Append(" AND UPPER(BALANCE)='TRUE' ");
                query = new StringBuilder(QAAdmin.Util.QueryUtil.GetInClauseQuery(lstSelectedDocId, query.ToString(), "DOCID"));
                sql = query.ToString();
                /*if (QAController.JenisSPT == "1111 DM")
                    sql = @"select d.DOCID as DOCID, d.BARCODE as BARCODE, d.DM_NAMAPKP_11 as NAMAPKP, d.DM_NPWP_11 as NPWP, d.DM_MASA1_11 as MASA1, d.DM_MASA2_11 as MASA2, d.DM_TAHUN_11 as TAHUN
                                from QA_USER.qa_job q right join dmindex_11 d on q.DOCID=d.DOCID 
                                WHERE q.DITUGASKANKEPADA is null and BALANCE='true' AND ROWNUM <= " + jumlahSPT;
                else
                    sql += @"select d.DOCID as DOCID, d.BARCODE as BARCODE, d.P_NAMAPKP_11 as NAMAPKP, d.P_NPWP_11 as NPWP, d.P_MASA1_11 as MASA1, d.P_MASA2_11 asa MASA2, d.P_TAHUN_11 as TAHUN
                                from QA_USER.qa_job q right join ppnindex_11 d on q.DOCID=d.DOCID 
                                WHERE q.DITUGASKANKEPADA is null and BALANCE='true' AND ROWNUM <= " + jumlahSPT;
                */
                //sql = QAAdmin.Util.QueryUtil.GetInClauseQuery(lstSelectedDocId, sql, "d.DOCID");
                if (!string.IsNullOrEmpty(sql))
                    sql = "SELECT * FROM (" + sql + ") WHERE ROWNUM <=" + jumlahSPT + " ORDER BY JENISSPT ";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAJob> retval = new List<QAJob>();
                    while (reader.Read())
                    {
                        QAJob j = new QAJob();
                        j.DocId = reader["DOCID"] is DBNull ? "" : reader["DOCID"].ToString();
                        j.Barcode = reader["BARCODE"] is DBNull ? "" : reader["BARCODE"].ToString();
                        j.NamaPTKP = reader["NAMAPKP"] is DBNull ? "" : reader["NAMAPKP"].ToString();
                        j.Npwp = reader["NPWP"] is DBNull ? "" : reader["NPWP"].ToString();
                        j.MasaPajak = reader["MASA1"] + " s/d " + reader["MASA2"] + " " + reader["TAHUN"];
                        j.DitugaskanKepada = reader["DITUGASKANKEPADA"] is DBNull ? "" : reader["DITUGASKANKEPADA"].ToString();
                        j.JenisSPT = reader["JENISSPT"] is DBNull ? "" : reader["JENISSPT"].ToString();

                        retval.Add(j);
                    }

                    return retval;
                }
            }
        }

        private static IList<String> GetTotalNewBalancedJob()
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                string sql = "";
                string[] jenisSPT = ConfigurationManager.AppSettings["JenisSPT"].Split(',');
                StringBuilder query = new StringBuilder("SELECT * FROM (");
                for (int i = 0; i < jenisSPT.Length; i++)
                {
                    string s = jenisSPT[i];
                    if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings[s]))
                    {
                        if (i != 0)
                        {
                            query.Append(" UNION ");
                        }
                        query.Append(ConfigurationManager.AppSettings[s]);
                    }
                }
                query.Append(")");
                query.Append(" WHERE DITUGASKANKEPADA is null ");
                query.Append(" AND UPPER(BALANCE)='TRUE' ");
                sql = query.ToString();

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    List<String> lstDocId = new List<string>();
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                        lstDocId.Add(reader[0].ToString());
                    return lstDocId;
                }
            }
        }

        internal static IList<QAJob> GetJobByPetugasAndStatusAndType(string namapetugas, string status, string tipe)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT job.JOBID, job.DOCID, job.NAMAPTKP, job.NPWP, job.MASAPAJAK, job.WAKTUPENUGASAN, job.DITUGASKANKEPADA, 
job.STATUSASAL, job.STATUSAKHIR, job.WAKTUPENGECEKAN, job.CATATAN, job.BARCODE, job.JENISSPT, sptperbaikan.JUMLAHPERBAIKAN 
FROM QA_JOB job LEFT OUTER JOIN VW_SPTPERBAIKAN sptperbaikan ON job.DOCID = sptperbaikan.DOCID AND job.BARCODE = sptperbaikan.BARCODE 
WHERE job.DITUGASKANKEPADA LIKE :DITUGASKANKEPADA AND job.STATUSASAL=:STATUSASAL";

                if (status.Equals("%"))
                {
                    List<QAStatus> lstQAStatus = Enum.GetValues(typeof(QAStatus)).Cast<QAStatus>().ToList<QAStatus>();
                    lstQAStatus.Remove(QAStatus.all);
                    lstQAStatus.Remove(QAStatus.none);
                    lstQAStatus.Remove(QAStatus.pass);
                    lstQAStatus.Remove(QAStatus.suspend);

                    sql += " AND (STATUSAKHIR IN (";

                    foreach (QAStatus qa in lstQAStatus)
                    {
                        sql += "'" + qa + "',";
                    }
                    sql = sql[sql.Length - 1].Equals(',') ? sql.Remove(sql.Length - 1) : sql;
                    sql += ") OR STATUSAKHIR IS NULL)";
                }
                else
                    sql += " AND (STATUSAKHIR LIKE '" + status + "')";

                sql += " ORDER BY job.WAKTUPENUGASAN DESC, job.JOBID, job.DOCID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DITUGASKANKEPADA", namapetugas);
                    cmd.Parameters.AddWithValue(":STATUSASAL", tipe);
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAJob> retval = new List<QAJob>();
                    while (reader.Read())
                    {
                        QAJob job = CreateQAJobObject(reader);

                        retval.Add(job);
                    }

                    return retval;
                }
            }

        }

        internal static IList<QAJob> GetJobByPetugas(string namapetugas)
        {

            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT job.JOBID, job.DOCID, job.NAMAPTKP, job.NPWP, job.MASAPAJAK, job.WAKTUPENUGASAN, job.DITUGASKANKEPADA, 
job.STATUSASAL, job.STATUSAKHIR, job.WAKTUPENGECEKAN, job.CATATAN, job.BARCODE, job.JENISSPT, sptperbaikan.JUMLAHPERBAIKAN 
FROM QA_JOB job LEFT OUTER JOIN VW_SPTPERBAIKAN sptperbaikan ON job.DOCID = sptperbaikan.DOCID AND job.BARCODE = sptperbaikan.BARCODE 
WHERE job.DITUGASKANKEPADA=:DITUGASKANKEPADA ORDER BY job.JOBID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DITUGASKANKEPADA", namapetugas);
                    //cmd.Parameters.AddWithValue(":JENISSPT", QAController.JenisSPT);
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAJob> retval = new List<QAJob>();
                    while (reader.Read())
                    {
                        QAJob job = CreateQAJobObject(reader);

                        retval.Add(job);
                    }

                    return retval;
                }
            }

        }

        internal static IList<QAReport> GetListJobReport(string jobId)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"select 
DITUGASKANKEPADA, JUMLAHSPT,   
MAX( DECODE( STATUSAKHIR, 'hold', JUMLAHSTATUS, 0 ) ) JUMLAHHOLD,
MAX( DECODE( STATUSAKHIR, 'pass', JUMLAHSTATUS, 0 ) ) JUMLAHPASS,
MAX( DECODE( STATUSAKHIR, 'suspend', JUMLAHSTATUS, 0 ) ) JUMLAHSUSPEND   
from                       
(select job.DITUGASKANKEPADA, job.JUMLAHSPT, job2.STATUSAKHIR, job2.JUMLAHSTATUS from (select DITUGASKANKEPADA, COUNT(DITUGASKANKEPADA) AS JUMLAHSPT from QA_JOB WHERE JOBID = :JOBID GROUP BY DITUGASKANKEPADA) job, 
(select DITUGASKANKEPADA, STATUSAKHIR, COUNT(STATUSAKHIR) as JUMLAHSTATUS from QA_JOB WHERE JOBID = :JOBID group by DITUGASKANKEPADA, STATUSAKHIR) job2 
where job.DITUGASKANKEPADA = job2.DITUGASKANKEPADA) group by DITUGASKANKEPADA, JUMLAHSPT order by DITUGASKANKEPADA";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":JOBID", jobId);
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAReport> retval = new List<QAReport>();
                    while (reader.Read())
                    {
                        QAReport report = new QAReport();
                        report.UserName = reader["DITUGASKANKEPADA"] is DBNull ? "" : reader["DITUGASKANKEPADA"].ToString();
                        report.JumlahSPT = reader["JUMLAHSPT"] is DBNull ? "" : reader["JUMLAHSPT"].ToString();
                        report.JumlahHold = reader["JUMLAHHOLD"] is DBNull ? "" : reader["JUMLAHHOLD"].ToString();
                        report.JumlahPass = reader["JUMLAHPASS"] is DBNull ? "" : reader["JUMLAHPASS"].ToString();
                        report.JumlahSuspend = reader["JUMLAHSUSPEND"] is DBNull ? "" : reader["JUMLAHSUSPEND"].ToString();
                        retval.Add(report);
                    }

                    return retval;
                }
            }
        }

        internal static IList<QAReportPenugasan> GetListJobReportPenugasan()
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT JOBID, WAKTUPENUGASAN, JUMLAHSPT, JUMLAHPERBAIKAN FROM VW_SPTPENUGASAN ORDER BY WAKTUPENUGASAN DESC";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAReportPenugasan> retval = new List<QAReportPenugasan>();
                    while (reader.Read())
                    {
                        QAReportPenugasan report = new QAReportPenugasan();
                        report.JobId = reader["JOBID"] is DBNull ? "" : reader["JOBID"].ToString();
                        report.WaktuPenugasan = reader["WAKTUPENUGASAN"] is DBNull ? "" : reader["WAKTUPENUGASAN"].ToString();
                        report.JumlahSPT = reader["JUMLAHSPT"] is DBNull ? "" : reader["JUMLAHSPT"].ToString();
                        report.JumlahPerbaikan = reader["JUMLAHPERBAIKAN"] is DBNull ? "" : reader["JUMLAHPERBAIKAN"].ToString();
                        retval.Add(report);
                    }

                    return retval;
                }
            }
        }

        internal static IList<Petugas> GetPetugasByTipeAndLogin(TipeSPT tipe, int login)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT LOGINID, USERGROUP, ISLOGIN FROM QA_USER 
                            WHERE USERGROUP=:USERGROUP AND ISLOGIN=:ISLOGIN";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":USERGROUP", tipe.ToString().ToLower());
                    cmd.Parameters.AddWithValue(":ISLOGIN", login);
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<Petugas> retval = new List<Petugas>();
                    while (reader.Read())
                    {
                        Petugas p = CreatePetugasObject(reader);

                        retval.Add(p);
                    }

                    return retval;
                }
            }
        }

        private static Petugas CreatePetugasObject(OracleDataReader reader)
        {
            Petugas p = new Petugas();
            p.Username = reader["LOGINID"].ToString();
            p.Group = reader["USERGROUP"].ToString();
            p.IsLogin = Convert.ToInt32(reader["ISLOGIN"]) == 1 ? true : false;
            return p;
        }

        internal static void CreateNewJobs(List<QAJob> lstJob)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"INSERT INTO QA_JOB (JOBID, DOCID, NAMAPTKP, NPWP, MASAPAJAK, WAKTUPENUGASAN, 
                                                   DITUGASKANKEPADA, STATUSASAL, CATATAN, JENISSPT, BARCODE)
                               VALUES(:JOBID, :DOCID, :NAMAPTKP, :NPWP, :MASAPAJAK, :WAKTUPENUGASAN, 
                                      :DITUGASKANKEPADA, :STATUSASAL, :CATATAN, :JENISSPT, :BARCODE)";


                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand(sql, con, tx);
                    foreach (QAJob j in lstJob)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue(":JOBID", j.JobID);
                        cmd.Parameters.AddWithValue(":DOCID", j.DocId);
                        cmd.Parameters.AddWithValue(":NAMAPTKP", j.NamaPTKP);
                        cmd.Parameters.AddWithValue(":NPWP", j.Npwp);
                        cmd.Parameters.AddWithValue(":MASAPAJAK", j.MasaPajak);
                        cmd.Parameters.AddWithValue(":WAKTUPENUGASAN", j.WaktuPenugasan);
                        cmd.Parameters.AddWithValue(":DITUGASKANKEPADA", j.DitugaskanKepada);
                        cmd.Parameters.AddWithValue(":STATUSASAL", j.StatusAsal);
                        cmd.Parameters.AddWithValue(":CATATAN", j.Catatan);
                        cmd.Parameters.AddWithValue(":JENISSPT", j.JenisSPT);
                        cmd.Parameters.AddWithValue(":BARCODE", j.Barcode);
                        cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
            }
        }

        internal static int DeleteJob(List<QAJob> jobs)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                int i = 0;
                foreach (QAJob job in jobs)
                {
                    string sql = @"DELETE FROM qa_job
                                  WHERE docid = :docid
                                    AND barcode = :barcode
                                    AND (docid, barcode) IN (SELECT docid, barcode
                                                            FROM qa_job job JOIN qa_user usr
                                                            ON job.ditugaskankepada = usr.loginid
                                                            WHERE islogin = :ISLOGIN OR statusakhir = :STATUSAKHIR)";

                    using (OracleCommand cmd = new OracleCommand(sql, con))
                    {
                        cmd.Parameters.AddWithValue(":DOCID", job.DocId);
                        cmd.Parameters.AddWithValue(":BARCODE", job.Barcode);
                        cmd.Parameters.AddWithValue(":ISLOGIN", 0);
                        cmd.Parameters.AddWithValue(":STATUSAKHIR", TipeSPT.Unbalance.ToString().ToLower());
                        if (cmd.ExecuteNonQuery() == 1) i++;
                    }
                }
                return i;
            }
        }

        internal static bool IsValidAdminLogin(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(LOGINID) FROM QA_USER 
                            WHERE LOGINID=:LOGINID AND (USERGROUP='administrator')";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    Decimal result = (Decimal)cmd.ExecuteScalar();

                    return result > 0;
                }
            }
        }

        internal static bool IsFirstTime()
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(LOGINID) FROM QA_USER";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    Decimal result = (Decimal)cmd.ExecuteScalar();

                    return result <= 0;
                }
            }
        }

        internal static bool IsValidSupervisor(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(LOGINID) FROM QA_USER 
                            WHERE LOGINID=:LOGINID AND (USERGROUP LIKE 'supervisor%')";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    Decimal result = (Decimal)cmd.ExecuteScalar();

                    return result > 0;
                }
            }
        }

        internal static TipeSPT GetTipeSPTFromUser(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT USERGROUP FROM QA_USER 
                            WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    OracleDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        if (reader["USERGROUP"].ToString().ToLower().Contains("unbalance"))
                            return TipeSPT.Unbalance;
                    }
                    return TipeSPT.Balance;
                }
            }
        }

        internal static IList<Petugas> GetAllPetugas()
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT LOGINID, USERGROUP, ISLOGIN FROM QA_USER";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<Petugas> retval = new List<Petugas>();
                    while (reader.Read())
                    {
                        Petugas p = CreatePetugasObject(reader);

                        retval.Add(p);
                    }

                    return retval;
                }
            }
        }

        internal static Petugas GetPetugasByName(string username)
        {
            Petugas p = new Petugas();
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT LOGINID, USERGROUP, ISLOGIN FROM QA_USER WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    OracleDataReader reader = cmd.ExecuteReader();
                    
                    if (reader.Read())
                    {
                        p = CreatePetugasObject(reader);                        
                    }
                }
            }
            return p;
        }

        internal static bool IsUsernameExist(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(LOGINID) FROM QA_USER WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    Decimal result = (Decimal)cmd.ExecuteScalar();
                    return result > 0;
                }
            }
        }

        internal static void HapusPetugas(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"DELETE FROM QA_USER WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /* internal static QAJob GetJobByDocId(string docId)
         {
             using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
             {
                 con.Open();
                 string sql = @"SELECT job.JOBID, job.DOCID, job.NAMAPTKP, job.NPWP, job.MASAPAJAK, job.WAKTUPENUGASAN, job.DITUGASKANKEPADA, 
 job.STATUSASAL, job.STATUSAKHIR, job.WAKTUPENGECEKAN, job.CATATAN, job.BARCODE, job.JENISSPT, sptperbaikan.JUMLAHPERBAIKAN 
 FROM QA_JOB job LEFT OUTER JOIN VW_SPTPERBAIKAN sptperbaikan ON job.DOCID = sptperbaikan.DOCID
 WHERE job.DOCID=:DOCID ORDER BY job.JOBID";

                 using (OracleCommand cmd = new OracleCommand(sql, con))
                 {
                     cmd.Parameters.AddWithValue(":DOCID", docId);
                     OracleDataReader reader = cmd.ExecuteReader();

                     if (reader.Read())
                     {
                         QAJob job = CreateQAJobObject(reader);
                         return job;
                     }
                     else
                         return null;
                 }
             }
         }*/

        internal static QAJob GetJobByDocIdAndBarcode(string docId, string barcode)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT job.JOBID, job.DOCID, job.NAMAPTKP, job.NPWP, job.MASAPAJAK, job.WAKTUPENUGASAN, job.DITUGASKANKEPADA, 
job.STATUSASAL, job.STATUSAKHIR, job.WAKTUPENGECEKAN, job.CATATAN, job.BARCODE, job.JENISSPT, sptperbaikan.JUMLAHPERBAIKAN 
FROM QA_JOB job LEFT OUTER JOIN VW_SPTPERBAIKAN sptperbaikan ON job.DOCID = sptperbaikan.DOCID AND job.BARCODE = sptperbaikan.BARCODE 
WHERE job.DOCID=:DOCID AND job.BARCODE=:BARCODE ORDER BY job.JOBID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    cmd.Parameters.AddWithValue(":BARCODE", barcode);
                    OracleDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        QAJob job = CreateQAJobObject(reader);
                        return job;
                    }
                    else
                        return null;
                }
            }
        }

        internal static void UpdateQAJob(string log, string originalLog, string statusAkhir, string docId, string barcode)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = con;
                    cmd.Transaction = tx;

                    string sql = @"UPDATE QA_JOB SET STATUSAKHIR=:STATUSAKHIR, 
                                        WAKTUPENGECEKAN=:WAKTUPENGECEKAN, CATATAN=:CATATAN
                                    WHERE DOCID=:DOCID AND BARCODE=:BARCODE";
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue(":STATUSAKHIR", statusAkhir);
                    cmd.Parameters.AddWithValue(":WAKTUPENGECEKAN", DateTime.Now);
                    StringBuilder sb = new StringBuilder(log + originalLog);
                    if (sb.Length > 4000) sb.Remove(4000, sb.Length - 4000);
                    cmd.Parameters.AddWithValue(":CATATAN", sb.ToString());
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    cmd.Parameters.AddWithValue(":BARCODE", barcode);
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
            }
        }

        internal static void UbahPetugas(Petugas petugas)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql;
                sql = @"UPDATE QA_USER SET USERGROUP=:USERGROUP, ISLOGIN=:ISLOGIN WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":USERGROUP", petugas.Group);
                    cmd.Parameters.AddWithValue(":ISLOGIN", petugas.IsLogin);
                    cmd.Parameters.AddWithValue(":LOGINID", petugas.Username);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        internal static void UbahStatusLoginPetugas(string username, int islogin)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql;
                sql = @"UPDATE QA_USER SET ISLOGIN=:ISLOGIN WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":ISLOGIN", islogin);
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        internal static void BuatPetugas(Petugas petugas)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"INSERT INTO QA_USER(LOGINID, USERGROUP)
                                    VALUES(:LOGINID, :USERGROUP)";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", petugas.Username);
                    cmd.Parameters.AddWithValue(":USERGROUP", petugas.Group);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        internal static bool DoesUserHaveQAJob(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(DOCID) FROM QA_JOB WHERE DITUGASKANKEPADA=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    Decimal result = (Decimal)cmd.ExecuteScalar();
                    return result > 0;
                }
            }
        }

        internal static DataTable GetTableWithFieldByField(OracleConnection con, string tableSource, string[] fieldName, string docId, string barcode)
        {
            string sFieldName = string.Empty;

            sFieldName = StringUtil.ConvertToString(fieldName);

            using (DataSet dsRet = new DataSet())
            {
                string sql = @"SELECT "
                                  + sFieldName +
                                 " FROM " + tableSource +
                                 " WHERE DOCID=:DOCID AND BARCODE=:BARCODE";

                con.Open();
                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    cmd.Parameters.AddWithValue(":BARCODE", barcode);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    adapter.Fill(dsRet, tableSource);
                }

                return dsRet.Tables[tableSource];
            }
        }

        internal static string Decrypt(string connectionString)
        {

            if (IsDecrypted(connectionString))
            {
                if (configFile.StartsWith(URI_PREFIX))
                {
                    LogUtil.WriteLog("Converting URI to absolute path");
                    configFile = configFile.Replace(URI_PREFIX, "");
                    configFile = configFile.Replace(URI_PATH_SEPARATOR, ABSOLUTE_PATH_SEPARATOR);
                    Util.LogUtil.WriteLog("\t" + configFile);
                }
                LogUtil.WriteLog("Encrypting connection string in the configuration file");
                string chiperText = CryptoHelper.Encrypt(connectionString);
                StringBuilder sb = new StringBuilder(File.ReadAllText(configFile));
                sb.Replace(connectionString, chiperText);
                File.WriteAllText(configFile, sb.ToString());
                return connectionString;
            }
            else
            {
                LogUtil.WriteLog("Decrypting connection string from the configuration file");
                return CryptoHelper.Decrypt(connectionString);
            }
        }

        private static bool IsDecrypted(string connectionString)
        {
            string upper = connectionString.ToUpper();
            return upper.Contains("PROVIDER") ||
                upper.Contains("DATA SOURCE") ||
                upper.Contains("USER ID");
        }
    }
}
